1. Technical Field
Present invention embodiments relate to data processing and, more particularly, to using cursors to retrieve and display data from columnar databases.
2. Discussion of the Related Art
Generally, a database is a structured collection of data and metadata that is stored on one or more storage devices. The data within the database may be logically organized according to a variety of data models, depending on the implementation. In one such example, a relational database, data is generally organized as a set of tables, wherein each table may comprise of a set of rows and columns. Each row represents a distinct object and each column represents a distinct attribute.
A Database Management System (DBMS) is a software system that controls access to the data in the database. The DBMS is configured to receive and process a variety of database commands, referred to as queries. One example of a query language for managing data in the relational database is Structured Query Language (SQL). Data is generally transmitted over a network in a row format. Even in a columnar database, column data is transformed into a row format for transmission over the network.
When a request for data to be inserted into a columnar database is made, data is arranged into rows for transmission over the network to a server. At the server, the data received as rows is again transformed into columns before being stored. Similarly, when a query is for selecting data from a database, selected result-set data is transmitted as rows for display at a client. At the client, the received result-set is parsed and unstitched to fetch values corresponding to each row for display in the column format.
Thus, data processing in a column format requires not only, conversion and creation of an additional copy in a row format, but also requires mapping or re-configuration of data records of column format into row format and vice-versa. However, columnar storage is still preferred over row databases, as this allows for selective columns of tables to be processed.
Moreover, when data is retrieved on a client in a sequence, data is retrieved at a location that a cursor points to. For example, a cursor may point to each row of data being fetched and continue pointing to the given row until data corresponding to one or more columns is retrieved for that row. Since data corresponding to each of the columns is stored as a separate page, the same cursor would move to a number of different pages for a given row. After moving through all of the pages of a given row, the cursor moves to a second row. Consequently, additional processing at both server and client locations is required to support data retrieval, limiting the use of many applications. Additional processing is particularly limiting in situations where a large amount of data is required to be retrieved (or transferred) from the server, given limited network bandwidth and limited client cache.